﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ExameRegister.Model;
using System.Reflection;
using System.Collections.ObjectModel;
using System.IO;

namespace ExameRegister.BO
{
    public class LoginBO
    {
        public string Login(string name, string idcard)
        {
            string result = string.Empty;
            string sql = string.Format(@"SELECT  emp.ID, ISNULL(info.Name,'') AS Name FROM dbo.Emp_BasicInfo emp
LEFT JOIN dbo.OrgForHR org ON emp.OrgID = org.ID
LEFT JOIN dbo.ExameOrg ex ON ex.OrgForHRID = org.ID
LEFT JOIN dbo.PublicOfficialInfo info ON ex.ExameID = info.ID AND  info.Status = 1 where emp.Name='{0}' and emp.CardNo='{1}'
ORDER BY info.Name DESC, emp.ID", name, idcard);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            int count = ds.Tables[0].Rows.Count;
            if (count > 0)
            {
                string exame = ds.Tables[0].Rows[0][1] as string;
                if (string.IsNullOrEmpty(exame))
                    return "您没有报考权限!";
                sql = string.Format("exec pro_LoginStatistic '" + idcard + "'");
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
                return null;
            }
            else
                return "姓名或证件号码错误";
        }

        public ObservableCollection<PublicOfficialInfo> GetPublicOfficialInfo(string condition)
        {
            string sql = "select * from PublicOfficialInfo";
            if (!string.IsNullOrEmpty(condition))
                sql += " where " + condition;
            ObservableCollection<PublicOfficialInfo> collection = new ObservableCollection<PublicOfficialInfo>();
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    PublicOfficialInfo entity = new PublicOfficialInfo();
                    entity.ID = (int)dr["ID"];
                    entity.Name = dr["Name"] as string;
                    entity.Infomation = dr["Infomation"] as string;
                    entity.InputName = dr["InputName"] as string;
                    entity.InputTime = dr["InputTime"] as DateTime?;
                    entity.DSC = dr["DSC"] as string;
                    entity.Type = (int)dr["Type"];
                    entity.Status = (int)dr["Status"];
                    entity.Office = dr["Office"] as string;
                    entity.YM = dr["YM"] as DateTime?;
                    collection.Add(entity);
                }
            }

            return collection;
        }

        public bool SavePublicOfficialInfo(PublicOfficialInfo entity)
        {
            if (entity.ID > 0)
                return UpdatePublicOfficialInfo(entity);
            string sql = string.Format(@"INSERT INTO [PublicOfficialInfo]
           ([Name]
           ,[Type]
           ,[Infomation]
           ,[DSC]
           ,[InputName]
           ,[InputTime]
           ,[DownloadFile]
           ,[Status])
     VALUES
           (@Name
           ,@Type
           ,@Infomation
           ,@DSC
           ,@InputName
           ,@InputTime
           ,@DownloadFile
           ,@Status)");
            List<SqlParameter> parameters = new List<SqlParameter>();
            foreach (PropertyInfo pi in entity.GetType().GetProperties())
            {
                if (pi.Name.Equals("ID"))
                    continue;
                object value = pi.GetValue(entity, null);
                parameters.Add(new SqlParameter("@" + pi.Name, value == null ? System.DBNull.Value : value));
            }
            int count = AccessHelper.GetInstance().ExecuteSql(sql, parameters.ToArray());
            if (count == 1)
                return true;
            return false;
        }

        private bool UpdatePublicOfficialInfo(PublicOfficialInfo entity)
        {
            string sql = string.Format(@"UPDATE [PublicOfficialInfo]
   SET [Name] = @Name
      ,[Type] = @Type
      ,[Infomation] = @Infomation
      ,[DSC] = @DSC
      ,[InputName] = @InputName
      ,[InputTime] = @InputTime
      ,[DownloadFile] = @DownloadFile
      ,[Status] = @Status
 WHERE ID= @ID");
            List<SqlParameter> parameters = new List<SqlParameter>();
            foreach (PropertyInfo pi in entity.GetType().GetProperties())
            {
                object value = pi.GetValue(entity, null);
                parameters.Add(new SqlParameter("@" + pi.Name, value == null ? System.DBNull.Value : value));
            }
            int count = AccessHelper.GetInstance().ExecuteSql(sql, parameters.ToArray());
            if (count == 1)
                return true;
            return false;
        }

        public bool DeletePublicOfficialInfo(int id)
        {
            string sql = "delete PublicOfficialInfo where id=" + id;

            int count = AccessHelper.GetInstance().ExecuteNonQuery(sql);
            if (count == 1)
                return true;
            return false;
        }

        public System.Collections.ObjectModel.ObservableCollection<Model.PublicOfficialSelection> GetPublicOfficialSelection(string condition)
        {
            ObservableCollection<Model.PublicOfficialSelection> collection = new ObservableCollection<PublicOfficialSelection>();
            string sql = @"select ROW_NUMBER() OVER(ORDER BY p.id ) Sec, p.*,o.Name OrgName from PublicOfficialSelection p inner join OrgForHR o on p.OrgID=o.ID ";
            if (!string.IsNullOrEmpty(condition))
            {
                sql += " where " + condition;
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    collection.Add(FillEntity(dr));
                }
            }
            return collection;
        }

        public static PublicOfficialSelection FillEntity(DataRow dr)
        {
            PublicOfficialSelection right = new PublicOfficialSelection();
            if (dr.Table != null && dr.Table.Columns.Contains("ExameID")) if (!string.IsNullOrEmpty(dr["ExameID"].ToString())) right.ExameID = Convert.ToInt32(dr["ExameID"]);
            if (dr.Table != null && dr.Table.Columns.Contains("Sec")) right.Sec = Convert.ToInt32(dr["Sec"]);
            right.ID = (int)dr["ID"];
            right.OrgID = (int)dr["OrgID"];
            right.Department = dr["Department"] as string;
            right.Duty = dr["Duty"] as string;
            right.DutyDescription = dr["DutyDescription"] as string;
            right.PeopleCount = dr["PeopleCount"] as string;
            right.Education = dr["Education"] as string;
            right.Political = dr["Political"] as string;
            right.FaBuType = dr["FaBuType"] as string;
            right.DutyQualifications = dr["DutyQualifications"] as string;
            right.Memo = dr["Memo"] as string;
            right.Status = Convert.ToInt32(dr["Status"]);
            right.CheckMemo = dr["CheckMemo"] as string;
            right.OrgName = dr["OrgName"] as string;
            right.IsXDJL = Convert.ToBoolean(dr["IsXDJL"]);
            right.IsJZXXB = Convert.ToBoolean(dr["IsJZXXB"]);
            right.IsLocked = Convert.ToBoolean(dr["IsLocked"]);
            right.Year = Convert.ToInt32(dr["Year"]);
            right.DutyLevel = dr["DutyLevel"] as string;
            right.DutyAttribute = dr["DutyAttribute"] as string;
            if (right.IsXDJL == true) right.XBLXString += "选调交流,";
            if (right.IsJZXXB == true) right.XBLXString += "竞争性选拔,";
            if (!string.IsNullOrEmpty(right.XBLXString))
                right.XBLXString = right.XBLXString.Substring(0, right.XBLXString.Length - 1);
            if (dr.Table != null && dr.Table.Columns.Contains("Department_Duty"))
                right.Department_Duty = dr["Department_Duty"] as string;
            switch (right.Status)
            {
                case 0:
                    right.StatusString = "未提交";
                    break;
                case 1:
                    right.StatusString = "已提交";
                    break;
                case 2:
                    right.StatusString = "已退回";
                    break;
                case 3:
                    right.StatusString = "已审核";
                    break;
                case 4:
                    right.StatusString = "已发布";
                    break;
            }

            return right;
        }

        public bool SavePublicOfficialSelection(Model.PublicOfficialSelection entity)
        {
            if (entity.ID > 0)
                return UpdatePublicOfficialSelection(entity);
            string sql = " insert into PublicOfficialSelection (";
            string args = string.Empty;
            string parameters = string.Empty;
            List<SqlParameter> list = new List<SqlParameter>();
            PropertyInfo[] pis = entity.GetType().GetProperties();
            foreach (PropertyInfo pi in pis)
            {
                if (pi.Name == "_XXDW")
                    continue;
                args += string.IsNullOrEmpty(args)? pi.Name: " ,"+ pi.Name;
                parameters += string.IsNullOrEmpty(parameters) ? "@" + pi.Name : " , @" + pi.Name;
                object value = pi.GetValue(entity, null);
                if (value == null)
                    value = System.DBNull.Value;
                list.Add(new SqlParameter("@" + pi.Name, value));
            }
            sql = sql + args + " ) values (" + parameters + " )";
            int count = AccessHelper.GetInstance().ExecuteNonQuery(sql, list.ToArray());
            if (count == 1)
                return true;
            return false;
        }

        private bool UpdatePublicOfficialSelection(Model.PublicOfficialSelection entity)
        {
            string sql = " update PublicOfficialSelection  set ";
            string args = string.Empty;
            List<SqlParameter> list = new List<SqlParameter>();
            PropertyInfo[] pis = entity.GetType().GetProperties();
            string wherecondition = " where ID=" + entity.ID;
            foreach (PropertyInfo pi in pis)
            {
                if (pi.Name == "ID")
                    continue;
                if (pi.Name == "_XXDW")
                    continue;
                args += string.IsNullOrEmpty(args) ? pi.Name + "=@" + pi.Name : " ," + pi.Name + "=@" + pi.Name;

                object value = pi.GetValue(entity, null);
                if (value == null)
                    value = System.DBNull.Value;
                list.Add(new SqlParameter("@" + pi.Name, value));
            }
            sql = sql + args + wherecondition;
            int count = AccessHelper.GetInstance().ExecuteNonQuery(sql, list.ToArray());
            if (count == 1)
                return true;
            return false;
        }

        public bool DeletePublicOfficialSelection(int id)
        {
            string sql = "delete PublicOfficialSelection where ID =" + id;
            int count = AccessHelper.GetInstance().ExecuteNonQuery(sql, null);
            if (count == 1)
                return true;
            return false;
        }

        public Emp_BasicInfoRegister GetEmp_BasicInfoRegister(string name, string idcard, int positionid)
        {
            string sql = string.Format("select * from Emp_BasicInfoRegister where name='{0}' and CardNo='{1}'", name, idcard);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            Emp_BasicInfoRegister entity = null;
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    entity = new Emp_BasicInfoRegister();
                    PropertyInfo[] pis = entity.GetType().GetProperties();
                    foreach (PropertyInfo pi in pis)
                    {
                        if (pi.Name == "IsValidate")
                            continue;
                        object value = dr[pi.Name];
                        if (value != null && !System.DBNull.Value.Equals(value))
                        {
                            pi.SetValue(entity, value, null);
                        }
                    }
                }
                
            }
            else
            {
                sql = string.Format(@"select --emp.ID,emp.Name EmpName,IsNull(emp.Gender, '') Gender,convert(varchar(10), emp.Birthday, 23) as Birthday, emp.Native, 
emp. *, o.Name OrgName,duty.dutyName as PositionName, duty.OKDate--CONVERT(varchar(12) , duty.OKDate, 23) OKDate
 ,duty.DutyLevel, duty.JobDate , duty.DutyAttribute---CONVERT(varchar(12) ,duty.JobDate, 23) JobDate  --, emp.HasPhoto, emp.PhotoName
 from Emp_BasicInfo emp
 inner join OrgForHR o on o.ID = emp.OrgID
 left join Emp_Duty duty on duty.EmpbasicID = emp.ID and duty.JobStatus='在任'
 where emp.CardNo='{1}' and emp.Name = '{0}'", name, idcard);
                ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    entity = new Emp_BasicInfoRegister() { Name = name, ID = 0, CardNo = idcard, PositionID = positionid};
                    foreach (var p in entity.GetType().GetProperties())
                    {
                        if (p.Name == "ID" || p.Name == "Phone")
                            continue;
                        if (ds.Tables[0].Columns.Contains(p.Name))
                        {
                            object value = ds.Tables[0].Rows[0][p.Name];
                            if (value != null && value != System.DBNull.Value)
                            {
                                p.SetValue(entity, value, null);
                            }
                        }
                    }
                    entity.EmpID = (int)ds.Tables[0].Rows[0]["ID"];
                    //entity.PhotoName = ds.Tables[0].Rows[0]["PhotoName"] as string;
                    //entity.HasPhoto = ds.Tables[0].Rows[0]["HasPhoto"] as bool?;
                    //entity.Gender = ds.Tables[0].Rows[0]["Gender"] as string;
                    //if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["Birthday"] as string))
                    //    entity.Birthday = DateTime.Parse(ds.Tables[0].Rows[0]["Birthday"] as string);
                    //entity.Native = ds.Tables[0].Rows[0]["Native"] as string;
                   
                    string Org = ds.Tables[0].Rows[0]["OrgName"] as string;
                    string Position = ds.Tables[0].Rows[0]["PositionName"] as string;
                    if (!string.IsNullOrEmpty(Org) && !string.IsNullOrEmpty(Position))
                    {
                        entity.CurrentJobAndPosition = Org + ":" + Position;
                    }
                    else
                    {
                        entity.CurrentJobAndPosition = Org + Position;
                    }
                    //if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["OKDate"] as string))
                    //    entity.OKDate = DateTime.Parse(ds.Tables[0].Rows[0]["OKDate"] as string);
                    //entity.DutyLevel = ds.Tables[0].Rows[0]["DutyLevel"] as string;
                    //if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["JobDate"] as string))
                    //    entity.JobDate = DateTime.Parse(ds.Tables[0].Rows[0]["JobDate"] as string);
                    entity.KaoHeTime1 = new DateTime(DateTime.Now.Year - 1, 1, 1);
                    entity.KaoHeTime2 = new DateTime(DateTime.Now.Year - 2, 1, 1);
                    entity.KaoHeTime3 = new DateTime(DateTime.Now.Year - 3, 1, 1);
                    #region 教育信息

                    string EdSql = string.Format(@"select * from Emp_Education ed where IsTopEducation=1 and EmpBasicID={0};
 select Isnull(DegreeName, '') as DegreeName, Isnull(LearningType, '') as LearningType, 
 Isnull(School, '') as School, Isnull(Professional, '') as Professional from Emp_Degree where EmpbasicID={0}", entity.EmpID);
                    DataSet Edds = AccessHelper.GetInstance().ExcuteDataSet(EdSql);
                    #region 学位信息
                    List<DataRow> listFull = new List<DataRow>();
                    List<DataRow> job = new List<DataRow>();
                    if (Edds.Tables[1].Rows.Count > 0)
                    {
                        foreach (DataRow dr in Edds.Tables[1].Rows)
                        {
                            string type = dr["LearningType"] as string;
                            if (type.Equals("正规高等院校（全日制）"))
                            {
                                listFull.Add(dr);
                            }
                            else
                            {
                                job.Add(dr);
                            }
                        }
                        if (listFull.Count > 0)
                        {
                            DataRow dr = GetTopDegree(listFull);
                            if (dr != null)
                            {
                                string school = dr["School"] as string;
                                string majoy = dr["Professional"] as string;
                                string s_m = string.Empty;
                                if (!string.IsNullOrEmpty(school) && !string.IsNullOrEmpty(majoy))
                                {
                                    s_m = school + ":" + majoy;
                                }
                                else
                                {
                                    s_m = school + majoy;
                                }
                                entity.FullDegree = dr["DegreeName"] as string;
                                entity.FullSchoolAndMajor = s_m;
                                //SetCellValue(sheet1, 14, 'E', dr, "DegreeName");
                                //SetCellValue(sheet1, 13, 'K', s_m);
                            }
                        }
                        if (job.Count > 0)
                        {
                            DataRow dr = GetTopDegree(job);
                            if (dr != null)
                            {
                                string school = dr["School"] as string;
                                string majoy = dr["Professional"] as string;
                                string s_m = string.Empty;
                                if (!string.IsNullOrEmpty(school) && !string.IsNullOrEmpty(majoy))
                                {
                                    s_m = school + ":" + majoy;
                                }
                                else
                                {
                                    s_m = school + majoy;
                                }
                                entity.PartDegree = dr["DegreeName"] as string;
                                entity.PartSchoolAndMajor = s_m;
                                //SetCellValue(sheet1, 16, 'E', dr, "DegreeName");
                                //SetCellValue(sheet1, 15, 'K', s_m);
                            }
                        }

                    }



                    #endregion

                    #region 学历信息
                    if (Edds.Tables[0].Rows.Count > 0)
                    {
                        string educationtype = (Edds.Tables[0].Rows[0]["EducationType"] as string).Trim();
                        string school = Edds.Tables[0].Rows[0]["School"] as string;
                        string majoy = Edds.Tables[0].Rows[0]["Professional"] as string;
                        string s_m = string.Empty;
                        if (!string.IsNullOrEmpty(school) && !string.IsNullOrEmpty(majoy))
                        {
                            s_m = school + ":" + majoy;
                        }
                        else
                        {
                            s_m = school + majoy;
                        }
                        if (educationtype.Equals("正规高等院校（全日制）"))
                        {
                            entity.FullEducationBg = Edds.Tables[0].Rows[0]["EducationName"] as string;
                            entity.FullSchoolAndMajor = s_m;
                            //SetCellValue(sheet1, 13, 'E', Edds.Tables[0].Rows[0], "EducationName");
                            //SetCellValue(sheet1, 13, 'K', s_m);
                        }
                        else
                        {
                            entity.PartEducationBg = Edds.Tables[0].Rows[0]["EducationName"] as string;
                            entity.PartSchoolAndMajor = s_m;
                            //SetCellValue(sheet1, 15, 'E', Edds.Tables[0].Rows[0], "EducationName");
                            //SetCellValue(sheet1, 15, 'K', s_m);
                        }
                    }
                    #endregion



                    #endregion
                }
                //int empid = (int)AccessHelper.GetInstance().ExcuteDataSet(sql).Tables[0].Rows[0][0];
                //entity = new Emp_BasicInfoRegister() { Name = name, ID = 0, CardNo = idcard, PositionID = positionid, EmpID = empid };
            }
            return entity;
        }

        private DataRow GetTopDegree(List<DataRow> list)
        {
            DataRow drMaster = null;
            DataRow drBecholor = null;
            foreach (DataRow dr in list)
            {
                string degree = dr["DegreeName"] as string;
                if (degree.Contains("博士"))
                {
                    return dr;
                }
                if (degree.Contains("硕士"))
                {
                    drMaster = dr;
                }
                if (degree.Contains("学士"))
                {
                    drBecholor = dr;
                }


            }
            if (drMaster != null)
            {
                return drMaster;
            }
            if (drBecholor != null)
                return drBecholor;
            return null;
        }

        public bool SaveEmp_BasicInfoRegister(Model.Emp_BasicInfoRegister entity)
        {
            if (entity.ID > 0)
                return UpdateEmp_BasicInfoRegister(entity);
            string sql = " insert into Emp_BasicInfoRegister (";
            string args = string.Empty;
            string parameters = string.Empty;
            List<SqlParameter> list = new List<SqlParameter>();
            PropertyInfo[] pis = entity.GetType().GetProperties();
            foreach (PropertyInfo pi in pis)
            {
                if (pi.Name == "ID" || pi.Name == "IsValidate")
                    continue;
                args += string.IsNullOrEmpty(args) ? pi.Name : " ," + pi.Name;
                parameters += string.IsNullOrEmpty(parameters) ? "@" + pi.Name : " , @" + pi.Name;
                object value = pi.GetValue(entity, null);
                if (value == null)
                    value = System.DBNull.Value;
                list.Add(new SqlParameter("@" + pi.Name, value));
            }
            sql = sql + args + " ) values (" + parameters + " )";
            int count = AccessHelper.GetInstance().ExecuteNonQuery(sql, list.ToArray());
            if (count == 1)
                return true;
            return false;
        }

        private bool UpdateEmp_BasicInfoRegister(Model.Emp_BasicInfoRegister entity)
        {
            string sql = " update Emp_BasicInfoRegister  set ";
            string args = string.Empty;
            List<SqlParameter> list = new List<SqlParameter>();
            PropertyInfo[] pis = entity.GetType().GetProperties();
            string wherecondition = " where ID=" + entity.ID;
            foreach (PropertyInfo pi in pis)
            {
                if (pi.Name == "ID" || pi.Name == "IsValidate")
                    continue;
                args += string.IsNullOrEmpty(args) ? pi.Name + "=@" + pi.Name : " ," + pi.Name + "=@" + pi.Name;

                object value = pi.GetValue(entity, null);
                if (value == null)
                    value = System.DBNull.Value;
                list.Add(new SqlParameter("@" + pi.Name, value));
            }
            sql = sql + args + wherecondition;
            int count = AccessHelper.GetInstance().ExecuteNonQuery(sql, list.ToArray());
            if (count == 1)
                return true;
            return false;
        }

        public ObservableCollection<RegisterPosition> GetRegisterPosition(string cardno, string name, int status, int type, int ExameID)
        {
            ObservableCollection<RegisterPosition> collection = new ObservableCollection<RegisterPosition>();
            string sql = string.Empty;
            if (type == 1)
            { //选调交流

                sql = string.Format(@"select r.ID, p.ID as PositionID, emp.ID as EmpID, emp.Name, emp.CardNo, 
Isnull(r.[GeRenStatus], 5) [Status], p.Department AS XXDW, O.Name as Department, p.Duty, p.PeopleCount, 
p.Education, p.Political, r.[CheckMemo],p.DepartMent_Duty
from PublicOfficialSelection p
left join (select r.ID,r.EmpID, e.Name, e.CardNo, 
r.[GeRenStatus], r.PublicOfficialSelectionID, r.[CheckMemo] from dbo.PublicOfficialRecord r 
inner join Emp_BasicInfo e on e.ID = r.EmpID and e.CardNo='{0}' and e.Name='{1}'  ) r on r.PublicOfficialSelectionID = p.ID
left join OrgForHR o on p.OrgID =o.ID  
inner join Emp_BasicInfo emp on emp.CardNo='{0}' and emp.Name='{1}'
where p.Status={2} and p.FaBuType='选调交流'  and IsLocked=0 and p.ExameID={3}", cardno, name, status, ExameID);

                if (name.Equals("xuhuiqu"))
                {
                    sql = string.Format(@"select 0 ID, p.ID as PositionID, 0 as EmpID, 'xuhuiqu' Name, '' CardNo, 
5 [Status], p.Department AS XXDW, o.Name as Department, p.Duty, p.PeopleCount, 
p.Education, p.Political, '' [CheckMemo], p.DepartMent_Duty
from PublicOfficialSelection p
inner join OrgForHR o on p.OrgID = o.ID
--left join (select r.ID,r.EmpID, e.Name, e.CardNo, 
--r.[GeRenStatus], r.PublicOfficialSelectionID, r.[CheckMemo] from dbo.PublicOfficialRecord r 
--inner join Emp_BasicInfo e on e.ID = r.EmpID --and e.CardNo='{0}' and e.Name='{1}'  
--) r on r.PublicOfficialSelectionID = p.ID
--left join OrgForHR o on p.OrgID =o.ID  
--inner join Emp_BasicInfo emp on emp.CardNo='{0}' and emp.Name='{1}'
where p.Status={2} and p.FaBuType='选调交流'  and IsLocked=0 and p.ExameID={3}", cardno, name, status, ExameID);
                }

            }
            else if (type == 0)
            {
                sql = string.Format(@"select r.ID, p.ID as PositionID, emp.ID as EmpID, emp.Name, emp.CardNo, 
Isnull(r.[GeRenStatus], 5) [Status],p.Department AS XXDW,  O.Name as Department, p.Duty, p.PeopleCount, 
p.Education, p.Political, r.[CheckMemo],p.DepartMent_Duty
from PublicOfficialSelection p
left join (select r.ID,r.EmpID, e.Name, e.CardNo, 
r.[GeRenStatus], r.PublicOfficialSelectionID, r.[CheckMemo] from dbo.PublicOfficialRecord r 
inner join Emp_BasicInfo e on e.ID = r.EmpID and e.CardNo='{0}' and e.Name='{1}'  ) r on r.PublicOfficialSelectionID = p.ID
left join OrgForHR o on p.OrgID =o.ID  
inner join Emp_BasicInfo emp on emp.CardNo='{0}' and emp.Name='{1}'
where p.Status={2} and p.FaBuType='竞争性选拔'  and IsLocked=0 and p.ExameID={3}", cardno, name, status, ExameID);
                if (name.Equals("xuhuiqu"))
                {
                    sql = string.Format(@"select 0 ID, p.ID as PositionID, 0 as EmpID, '{1}' Name, '{0}' CardNo, 
5 [Status], p.Department AS XXDW,  O.Name as Department, p.Duty, p.PeopleCount, 
p.Education, p.Political, '' [CheckMemo], p.DepartMent_Duty
from PublicOfficialSelection p
--left join (select r.ID,r.EmpID, e.Name, e.CardNo, 
--r.[GeRenStatus], r.PublicOfficialSelectionID, r.[CheckMemo] from dbo.PublicOfficialRecord r 
--inner join Emp_BasicInfo e on e.ID = r.EmpID --and e.CardNo='{0}' and e.Name='{1}' 
--) r on r.PublicOfficialSelectionID = p.ID
inner join OrgForHR o on p.OrgID =o.ID  
--inner join Emp_BasicInfo emp on emp.CardNo='{0}' and emp.Name='{1}'
where p.Status={2} and p.FaBuType='竞争性选拔'  and IsLocked=0 and p.ExameID={3}", cardno, name, status, ExameID);
                }
            }

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    RegisterPosition entity = new RegisterPosition();
                    PropertyInfo[] pis = entity.GetType().GetProperties();
                    foreach (PropertyInfo pi in pis)
                    {
                        if (dr[pi.Name] != null && !System.DBNull.Value.Equals(dr[pi.Name]))
                        {
                            pi.SetValue(entity, dr[pi.Name], null);
                        }
                    }
                    collection.Add(entity);
                }
            }

            return collection;
        }

        public bool SaveRegisterPosition(RegisterPosition entity)
        {
            if (entity.ID > 0)
                return UpdateRegisterPosition(entity);
            string sql = string.Format(@"INSERT INTO [RegisterPosition]
           ([PositionID]
           ,[EmpID]
           ,[Name]
           ,[CardNo]
           ,[Status])
     VALUES
           (@PositionID
           ,@EmpID
           ,@Name
           ,@CardNo
           ,@Status)");
            List<SqlParameter> lsitP = new List<SqlParameter>();
            lsitP.Add(new SqlParameter("@PositionID", entity.PositionID));
            lsitP.Add(new SqlParameter("@EmpID", entity.EmpID));
            lsitP.Add(new SqlParameter("@Name", entity.Name));
            lsitP.Add(new SqlParameter("@CardNo", entity.CardNo));
            lsitP.Add(new SqlParameter("@Status", entity.Status));
            int count = AccessHelper.GetInstance().ExecuteNonQuery(sql, lsitP.ToArray());
            if (count == 1)
                return true;
            return false;
        }

        private bool UpdateRegisterPosition(RegisterPosition entity)
        {
            string sql = string.Format(@"UPDATE [RegisterPosition]
   SET [PositionID] = @PositionID
      ,[EmpID] = @EmpID
      ,[Name] = @Name
      ,[CardNo] = @CardNo
      ,[Status] = @Status
 WHERE ID=@ID");
            List<SqlParameter> lsitP = new List<SqlParameter>();
            lsitP.Add(new SqlParameter("@PositionID", entity.PositionID));
            lsitP.Add(new SqlParameter("@EmpID", entity.EmpID));
            lsitP.Add(new SqlParameter("@Name", entity.Name));
            lsitP.Add(new SqlParameter("@CardNo", entity.CardNo));
            lsitP.Add(new SqlParameter("@Status", entity.Status));
            lsitP.Add(new SqlParameter("@ID", entity.ID));
            int count = AccessHelper.GetInstance().ExecuteNonQuery(sql, lsitP.ToArray());
            if (count == 1)
                return true;
            return false;
        }

        public bool DeleteRegisterPosition(int id)
        {
            string sql = "delete RegisterPosition where id=" + id;
            int count = AccessHelper.GetInstance().ExecuteNonQuery(sql);
            if (count == 1)
                return true;
            return false;
        }

        public bool Register(RegisterPosition rp, Emp_BasicInfoRegister entity, int examenationid)
        {
            if (rp != null && entity != null)
            {
                try
                {
                    //时间判断
                    //string sql = string.Format("select * from PublicOfficialInfo where [Status] =1 and ID={0}", examenationid);
                    string sql = string.Format(@"select COUNT(*) from PublicOfficialRecord p
inner join Emp_BasicInfo e on p.EmpID = e.ID
inner join PublicOfficialSelection s on s.ID = p.PublicOfficialSelectionID and s.IsLocked = 0
where e.Name='{0}' and e.CardNo='{1}' and p.GeRenStatus in (0, 1)", entity.Name, entity.CardNo);
                    DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                    if ((int)ds.Tables[0].Rows[0][0] <= 0)
                    {
                    //    if (entity.ID <= 0)
                    //        this.InsertPublicOfficialRecord(rp);
                    //    else
                    //        this.UpdatePublicOfficialRecord(rp);
                        //this.SaveRegisterPosition(rp);
                        this.SaveEmp_BasicInfoRegister(entity);
                    }
                    else
                        return false;
                    
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
            
            return true;
        }

        private void InsertPublicOfficialRecord(RegisterPosition rp)
        {
            string sql = "insert into PublicOfficialRecord ([EmpID], [PublicOfficialSelectionID], [GeRenStatus]) values (@EmpID, @PositionID, @Status)";
            string[] param = new string[] { "EmpID", "PositionID", "Status" };
            List<SqlParameter> list = new List<SqlParameter>();
            foreach (string p in param)
            {
                PropertyInfo pi = rp.GetType().GetProperty(p);
                object value = pi.GetValue(rp, null);
                if (value == null)
                    value = System.DBNull.Value;
                list.Add(new SqlParameter("@" + p, value));
            }
            AccessHelper.GetInstance().ExecuteNonQuery(sql, list.ToArray());
        }

        private void UpdatePublicOfficialRecord(RegisterPosition rp)
        {
            string sql = "update PublicOfficialRecord set [GeRenStatus] = @Status where EmpID=@EmpID and  PublicOfficialSelectionID=@PositionID";
            string[] param = new string[] { "EmpID", "PositionID", "Status" };
            List<SqlParameter> list = new List<SqlParameter>();
            foreach (string p in param)
            {
                PropertyInfo pi = rp.GetType().GetProperty(p);
                object value = pi.GetValue(rp, null);
                if (value == null)
                    value = System.DBNull.Value;
                list.Add(new SqlParameter("@" + p, value));
            }
            AccessHelper.GetInstance().ExecuteNonQuery(sql, list.ToArray());
        }
        

        private bool UpdateRegisterPositionByCardNoAndName(RegisterPosition entity)
        {
            string sql = string.Format(@"UPDATE [RegisterPosition]
   SET [EmpID] = @EmpID      
      ,[Status] = @Status
 WHERE [Name] = @Name
      and [CardNo] = @CardNo and [PositionID] = @PositionID");
            List<SqlParameter> lsitP = new List<SqlParameter>();
            lsitP.Add(new SqlParameter("@PositionID", entity.PositionID));
            lsitP.Add(new SqlParameter("@EmpID", entity.EmpID));
            lsitP.Add(new SqlParameter("@Name", entity.Name));
            lsitP.Add(new SqlParameter("@CardNo", entity.CardNo));
            lsitP.Add(new SqlParameter("@Status", entity.Status));
           
            int count = AccessHelper.GetInstance().ExecuteNonQuery(sql, lsitP.ToArray());
            if (count == 1)
                return true;
            return false;
        }

        /// <summary>
        /// 报名条件判断
        /// 已报三个职位，且
        /// 或有一个职位审核通过
        /// </summary>
        /// <param name="positionid"></param>
        /// <param name="name"></param>
        /// <param name="cardno"></param>
        /// <returns></returns>
        public string JudgeCodition(int empid, string name, string cardno, int positionID, int status, int ExameID)
        {
            int count = 0;
            if (status == 0)//报名
            {
                //判断是否有权限报考该岗位
                string sql = string.Format(@"SELECT s.DutyLevel, s.DutyAttribute FROM  dbo.PublicOfficialSelection s 
WHERE s.ID ={0}", positionID);
                try
                {
                    DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                    string PDutyLevel = ds.Tables[0].Rows[0][0] as string;
                    string PDutyAttribute = ds.Tables[0].Rows[0][1] as string;
                    sql = string.Format(@"SELECT emp.DutyLevel ,emp.DutyAttribute FROM dbo.Emp_BasicInfoRegister emp
WHERE emp.EmpID = {0}", empid);
                    ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                    string dutylevel = ds.Tables[0].Rows[0][0] as string;
                    string dutyAttri = ds.Tables[0].Rows[0][1] as string;
                    if (dutylevel == PDutyLevel && dutylevel =="科员")//科员可以报科员
                    {
                    } else if (dutylevel.Contains(PDutyLevel) && PDutyAttribute.Contains(dutyAttri) && dutylevel != "科员" && PDutyLevel != "科员")
                    {
                    }
                    else
                    {
                        return "您没有报考权限";
                    }
                }
                catch (Exception ex)
                {
                    return "信息不全";
                }

                //判断是否有审核通过的条目
                sql = string.Format(@"select count(r.id) from [PublicOfficialRecord] r 
                                    INNER join PublicOfficialSelection s on s.ID = r.PublicOfficialSelectionID   AND s.IsLocked=0
                where r.[EmpID]='{0}'   and  r.GeRenStatus = 1  ", empid);
                count = (int)AccessHelper.GetInstance().ExcuteDataSet(sql).Tables[0].Rows[0][0];
                if (count > 0)
                    return "已有审核通过岗位，不能再报名!";
                sql = string.Format(@"select count(r.id) from [PublicOfficialRecord] r 
                                    INNER join PublicOfficialSelection s on s.ID = r.PublicOfficialSelectionID   AND s.IsLocked=0
                where r.[EmpID]='{0}'   and  r.GeRenStatus = 0", empid);
                count = (int)AccessHelper.GetInstance().ExcuteDataSet(sql).Tables[0].Rows[0][0];
                if (count >= 3)
                    return "报名岗位超过3个，不能再报!";
            }
            else if (status == 3)
            {
                //判断是否有审核通过的条目
                string sql = string.Format("select count(*) from [PublicOfficialRecord] where [EmpID]='{0}'   and  GeRenStatus in (1) and [PublicOfficialSelectionID]={1}", empid, positionID);
                count = (int)AccessHelper.GetInstance().ExcuteDataSet(sql).Tables[0].Rows[0][0];
                if (count > 0)
                    return "该岗位已经被审核通过,不能撤回！";
            }
            if (BaoMing(empid, positionID, status, ExameID))
            {
                return null;
            }
            return "报名失败！";
        }

        private bool BaoMing(int empid, int positionID, int status, int ExameID)
        {
            string sql = string.Format(@"if Exists (select * from PublicOfficialRecord where EmpID={0} and PublicOfficialSelectionID={1})
begin
	update PublicOfficialRecord set GeRenStatus={2} where EmpID={0} and PublicOfficialSelectionID={1} and ExameID={3}
end
else
	insert into PublicOfficialRecord (EmpID, PublicOfficialSelectionID, GeRenStatus, ExameID) values({0},{1},{2}, {3})", empid, positionID, status, ExameID);
            int count = (int)AccessHelper.GetInstance().ExecuteNonQuery(sql);
            if (count == 1)
                return true;
            return false;
        }

        public List<Files> GetFiles(int parentid)
        {
            List<Files> list = new List<Files>();
            string sql = @"select * from [Files]
where ParentTableKey = " + parentid;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Files f = new Files();
                PropertyInfo[] pis = f.GetType().GetProperties();
                foreach (PropertyInfo p in pis)
                {
                    object value = dr[p.Name];
                    if (value != null && value != System.DBNull.Value)
                        p.SetValue(f, value, null);
                }
                list.Add(f);
            }
            return list;

        }
        private byte[] OneFile = null;
        public UploadFileInfo DownLoadFile(UploadFileInfo info)
        {
            string uploadFolder = System.Web.Configuration.WebConfigurationManager.AppSettings[info.key].ToString();
            if (File.Exists(uploadFolder + @"\" + info.Name))
            {
                ////if (info.start == 0)
                ////{
                //    OneFile = null;
                //    //OneFile = File.ReadAllBytes(uploadFolder + @"\" + info.Name);
                ////}
                //if (info.Context == null)
                //    info.Context = new byte[info.Size];
                //if (OneFile.Length <= info.start + info.Size)
                //    info.IsFinished = true;
                //if (OneFile.Length < info.start + info.Size)
                //{
                //    info.Context = new byte[OneFile.Length - info.start];
                //}
                int b = 0;
                using (FileStream fs = File.Open(uploadFolder + @"\" + info.Name, FileMode.Open))
                {
                    fs.Position = info.start;
                    if (info.Context == null)
                        info.Context = new byte[info.Size];
                    if (fs.Length <= info.start + info.Size)
                        info.IsFinished = true;
                    if (fs.Length < info.start + info.Size)
                    {
                        info.Context = new byte[fs.Length - info.start];
                    }
                    fs.Read(info.Context, 0, info.Context.Length);
                }
                //while (stream.Position > -1 && stream.Position < stream.Length)
                //{
                //    if (stream.Length - stream.Position >= 16000)
                //    {
                //        b = 16000;
                //    }
                //    else
                //    {
                //        b = (int)(stream.Length - stream.Position);
                //    }

                //    byte[] filebyte = new byte[b];
                //    stream.Read(filebyte, 0, b);
                //    uploadFile.Context.Add(filebyte);
                //}
                //for (int i = info.start; i < OneFile.Length && i < info.start + info.Size; i++)
                //{
                //    info.Context[i - info.start] = OneFile[i];
                //}
               
                return info;
                //return new ObservableCollection<byte>(File.ReadAllBytes(uploadFolder + @"\" + info.Name));
            }else
             return null;
        }

        public bool HasWritCV(int empid, string name, string idcard)
        {
            string sql = string.Format("select count(*) from dbo.Emp_BasicInfoRegister where name='{0}' and CardNo='{1}'", name, idcard);
            int count = (int)AccessHelper.GetInstance().ExcuteDataSet(sql).Tables[0].Rows[0][0];
            if (count == 1)
                return true;
            return false;
        }

        #region 数据字典
//        /// <summary>
//        /// 保存
//        /// </summary>
//        /// <param name="DataModel"></param>
//        /// <param name="DDIM"></param>
//        /// <returns></returns>
//        public int SaveDataDictionary(DataDictionaryModel DataModel, ObservableCollection<DataDictionaryItemModel> DDIM)
//        {
//            //事务开始
//            //SqlConnection conn = null;
//            //SqlTransaction Sc = AccessHelper.GetInstance().BeginTransaction(out conn);
//            string sql = "";
//            int PID = 0;
//            if (DataModel.Id == 0)
//            //插入主表
//            {
//                sql = string.Format(@"insert into DataDictionary(DictName,DictCode,IsDynamic,DynamicSQL,CanDelete,IsSystem,IsAutoLoad,IsTree) values(
//                                           '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",
//                                                DataModel.DictName, DataModel.DictCode, DataModel.IsDynamic, DataModel.DynamicSQL, DataModel.CanDelete, DataModel.IsSystem,
//                                                DataModel.IsAutoLoad, DataModel.IsTree);
//                //返回当前索引
//                PID = int.Parse(AccessHelper.GetInstance().ExcuteDataSet(sql + "select @@identity from DataDictionary").Tables[0].Rows[0][0].ToString());

//            }
//            else
//            {
//                sql = string.Format(@"update DataDictionary set DictName='{0}',DictCode='{1}',IsDynamic='{2}',DynamicSQL='{3}',CanDelete='{4}',IsSystem='{5}',IsAutoLoad='{6}',IsTree='{7}' where ID={8}", DataModel.DictName, DataModel.DictCode, DataModel.IsDynamic, DataModel.DynamicSQL, DataModel.CanDelete, DataModel.IsSystem,
//                                                DataModel.IsAutoLoad, DataModel.IsTree, DataModel.Id);
//                PID = DataModel.Id;

//                AccessHelper.GetInstance().ExcuteDataSet(sql + "select @@identity from DataDictionary").Tables[0].Rows[0][0].ToString();
//            }



//            //int PID = int.Parse(AccessHelper.GetInstance().ExcuteDataSet("select @@identity from DataDictionary").Tables[0].Rows[0][0].ToString());

//            if (null != DDIM)
//            {
//                ///插入项
//                foreach (var item in DDIM)
//                {
//                    //新增的才插入
//                    if (item.Id == 0)
//                        AccessHelper.GetInstance().ExecuteNonQuery(string.Format(@"insert into DataDictionaryItem(DictCode,DictValue,DictPinYin,ParentId) values('{0}','{1}','{2}',{3})", item.DictCode, item.DictValue, item.DictPinYin, PID));
//                }
//            }

//            //结束
//            //AccessHelper.GetInstance().EndTransaction(Sc);

//            return PID;
//        }

        


//        /// <summary>
//        /// 
//        /// </summary>
//        /// <returns></returns>
//        public IList<DataDictionaryItemModel> GetDataDictionaryItem(int PID)
//        {
//            var listbysql = new List<DataDictionaryItemModel>();
//            DataSet ds = new AccessHelper().ExcuteDataSet(string.Format("select * from DataDictionaryItem where ParentID={0}", PID));
//            foreach (DataRow r in ds.Tables[0].Rows)
//            {
//                listbysql.Add(new DataDictionaryItemModel() { DictValue = r["DictValue"].ToString(), DictCode = r["DictCode"].ToString(), Id = Convert.ToInt32(r["Id"].ToString()), DictPinYin = r["DictPinYin"].ToString() });

//            }
//            return listbysql;
//        }

//        /// <summary>
//        /// 
//        /// </summary>
//        /// <returns></returns>
//        public IList<DataDictionaryModel> GetDataDictionary(int PID)
//        {
//            var listbysql = new List<DataDictionaryModel>();
//            DataSet ds = new AccessHelper().ExcuteDataSet(string.Format("select * from DataDictionary where ID={0}", PID));
//            foreach (DataRow r in ds.Tables[0].Rows)
//            {
//                listbysql.Add(new DataDictionaryModel() { DictName = r["DictName"].ToString(), DictCode = r["DictCode"].ToString(), Id = Convert.ToInt32(r["Id"].ToString()), IsDynamic = Convert.ToBoolean(r["IsDynamic"]), IsTree = Convert.ToBoolean(r["IsTree"]), DynamicSQL = r["DynamicSQL"].ToString() });

//            }
//            return listbysql;
//        }

//        /// <summary>
//        /// 删除
//        /// </summary>
//        /// <returns></returns>
//        public int DeleteDataDictionary(int PID)
//        {
//            //删除子集
//            new AccessHelper().ExecuteNonQuery(string.Format(@"delete from DataDictionaryItem where ParentID={0}", PID));
//            //删除父级并返回
//            return new AccessHelper().ExecuteNonQuery(string.Format(@"delete from DataDictionary where ID={0}", PID));
//        }


//        /// <summary>
//        /// 删除
//        /// </summary>
//        /// <param name="ID"></param>
//        /// <returns></returns>
//        public int DeleteDataDictionaryItem(int ID)
//        {
//            return new AccessHelper().ExecuteNonQuery(string.Format(@"delete from DataDictionaryItem where ID={0}", ID));
//        }

//        public IList<DictionaryModel> GetSpouseByPID(string parentid)
//        {
//            IList<DictionaryModel> list = new List<DictionaryModel>();


//            DataSet ds = new DataSet();
//            try
//            {
//                ds = new AccessHelper().ExcuteDataSet(string.Format(@"select [Id],'' AS Value,'' AS ParentKey ,[DictName] as Name,[DictCode] as Code,[IsDynamic] as Type,[IsTree],[DynamicSQL] as SQL,0 AS [Sequence],'' AS [Key] from DataDictionary WHERE DICTCODE='{0}' Order by Dictname", parentid));
//            }
//            catch (Exception ex)
//            {

//            }
//            if (ds.Tables[0].Rows[0]["Type"].ToString() == "False")
//            {

//                DataSet ds1 = new AccessHelper().ExcuteDataSet(string.Format(@"select di.Id ,'' as Value, di.DictValue as Name,di.DictCode as Code,[IsDynamic] as Type,[IsTree],
//      [DynamicSQL] as SQL,'' as ParentKey,0 AS [Sequence],'' as [Key] from DataDictionary d
//      inner join DataDictionaryItem di on d.Id = di.ParentId WHERE d.DICTCODE='{0}'
//      Order by Dictname", parentid));

//                foreach (DataRow dr in ds1.Tables[0].Rows)
//                {
//                    DictionaryModel model = new DictionaryModel();

//                    model.Code = dr["Code"].ToString();
//                    model.Id = int.Parse(dr["Id"].ToString());
//                    model.IsTree = Convert.ToBoolean(dr["IsTree"].ToString());
//                    model.Key = dr["Key"].ToString();
//                    model.Name = dr["Name"].ToString();
//                    model.ParentKey = dr["ParentKey"].ToString();
//                    model.Sequence = int.Parse(dr["Sequence"].ToString());
//                    model.SQL = dr["SQL"].ToString();
//                    model.Type = 0;
//                    model.Value = dr["Value"].ToString();
//                    //model.
//                    list.Add(model);

//                }
//            }

//            else
//            {
//                DataSet ds2 = new AccessHelper().ExcuteDataSet(ds.Tables[0].Rows[0]["SQL"].ToString());

//                if (ds2.Tables[0].Rows.Count > 0)
//                {
//                    var listbysql = new List<DictionaryModel>();
//                    foreach (DataRow r in ds2.Tables[0].Rows)
//                    {
//                        listbysql.Add(new DictionaryModel() { Name = r["DictValue"].ToString(), Code = r["DictCode"].ToString(), Id = Convert.ToInt32(r["Id"].ToString()) });
//                    }
//                    list = listbysql;
//                }
//            }

//            return list;
//        }

//        public IList<DictionaryModel> GetDictionarys()
//        {
//            var listbysql = new List<DictionaryModel>();
//            DataSet ds = new AccessHelper().ExcuteDataSet("select Id, DictName,DictCode,case IsDynamic when 'false' then '静态' when 'true' then '动态' end as Type from DataDictionary");
//            foreach (DataRow r in ds.Tables[0].Rows)
//            {
//                listbysql.Add(new DictionaryModel() { Name = r["DictName"].ToString(), Code = r["DictCode"].ToString(), Id = Convert.ToInt32(r["Id"].ToString()), Value = r["Type"].ToString() });

//            }
//            return listbysql;
//        }

//        ///// <summary>
//        ///// 
//        ///// </summary>
//        ///// <returns></returns>
//        //public IList<DataDictionaryModel> GetDictionarys()
//        //{
//        //    var listbysql = new List<DataDictionaryModel>();
//        //    DataSet ds = new AccessHelper().ExcuteDataSet("select Id, DictName,DictCode,case IsDynamic when 'false' then '静态' when 'true' then '动态' end as Type from DataDictionary");
//        //    foreach (DataRow r in ds.Tables[0].Rows)
//        //    {
//        //        listbysql.Add(new DataDictionaryModel() { DictName = r["DictName"].ToString(), DictCode = r["DictCode"].ToString(), Id = Convert.ToInt32(r["Id"].ToString()), DynamicSQL = r["Type"].ToString() });

//        //    }
//        //    return listbysql;
//        //}

        public DataDictionaryModel GetDataDictionaryModel(string DictCode, string parameter)
        {
            DataDictionaryModel model = new DataDictionaryModel();
            string sql = null;
            SqlConnection conn = null;
            SqlTransaction transaction = AccessHelper.GetInstance().BeginTransaction(out conn);
            string DynamicSql = null;
            try
            {
                sql = string.Format(@"select IsTree, IsDynamic, Isnull(DynamicSQL, '') DynamicSQL from DataDictionary where DictCode='{0}'", DictCode);
                SqlDataReader reader = new SqlCommand(sql, conn, transaction).ExecuteReader();
                reader.Read();
                bool Istree = (bool)reader[0];
                bool IsDynamic = (bool)reader[1];
                DynamicSql = reader[2].ToString();
                reader.Close();
                DataSet ds = new DataSet();
                if (IsDynamic && !string.IsNullOrEmpty(DynamicSql))
                {
                    if (!string.IsNullOrEmpty(parameter))//参数转换
                    {
                        IEnumerable<string> parameters = parameter.Split(';').Where(c => c != "");
                        foreach (string p in parameters)
                        {
                            string[] KeyValue = p.Split('=');
                            string key = ":" + KeyValue[0];
                            string value = KeyValue[1];
                            DynamicSql = DynamicSql.Replace(key, value);
                        }
                    }

                    reader = new SqlCommand(DynamicSql, conn, transaction).ExecuteReader();
                    //adpeter.Fill(ds);
                }
                else if (!IsDynamic)
                {
                    DynamicSql = string.Format(@"select i.* from DataDictionary d
left join DataDictionaryItem i on i.ParentId = d.Id
where d.DictCode = '{0}'", DictCode);
                    //new SqlCommand().
                    //SqlDataAdapter adpeter = new SqlDataAdapter(DynamicSql, conn);
                    //adpeter.Fill(ds);
                    reader = new SqlCommand(DynamicSql, conn, transaction).ExecuteReader();
                }
                //DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                model.DictCode = DictCode;
                if (reader == null)
                    return null;
                while (reader.Read())
                {
                    DataDictionaryItemModel item = new DataDictionaryItemModel();
                    item.DictCode = reader["DictCode"].ToString();
                    item.DictValue = reader["DictValue"].ToString();
                    item.Id = (int)reader["Id"];
                    item.ParentId = (int)reader["ParentId"];
                    item.DictPinYin = reader["DictPinYin"].ToString();
                    model.Item.Add(item);
                }
                reader.Close();
                //foreach (DataRow dr in ds.Tables[0].Rows)
                //{
                //    //model.IsTree = (bool)dr["IsTree"];
                //    DataDictionaryItemModel item = new DataDictionaryItemModel();
                //    item.DictCode = dr["DictCode"].ToString();
                //    item.DictValue = dr["DictValue"].ToString();
                //    item.Id = (int)dr["id"];
                //    item.ParentId = (int)dr["ParentId"];
                //    item.DictPinYin = dr["DictPinYin"].ToString();
                //    model.Item.Add(item);
                //}
                model.IsTree = Istree;
                AccessHelper.GetInstance().EndTransaction(transaction);
            }
            catch (Exception ex)
            {
                model.DictCode = DynamicSql;//ex.Message;//throw ex;
            }
            return model;
        }
        
        #endregion
    }
}
